<?php
/**
 * Zend Framework Compatible PHP MSSQL Database Adapter
 *
 * LICENSE
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.opensource.org/licenses/bsd-license.php
 *
 * @category    Adapters
 * @package     Db
 * @subpackage  Adapter_Mssql
 * @copyright   Copyright (c) 2012 Zend Framework Compatible PHP MSSQL Database Adapter
 * @version     $Id: Mssql.php 6 2012-05-24 09:45:21Z mail@henryhayes.co.uk $
 * @since       2012-02-12
 */
/**
 * Adapters_Db_Adapter_Mssql class.
 *
 * A Zend Framework compatible adapter that wraps around the native mssql
 * adapter for PHP. {@link http://php.net/manual/en/book.mssql.php}
 *
 * @category    Adapters
 * @package     Db
 * @subpackage  Adapter_Mssql
 * @copyright   Copyright (c) 2012 Zend Framework Compatible PHP MSSQL Database Adapter
 * @author      Henry Hayes <mail@henryhayes.co.uk>
 * @since       2012-02-12
 */
class Adapters_Db_Adapter_Mssql extends Zend_Db_Adapter_Abstract
{

    /**
     * Keys are UPPERCASE SQL datatypes or the constants
     * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
     *
     * Values are:
     * 0 = 32-bit integer
     * 1 = 64-bit integer
     * 2 = float or decimal
     *
     * @var array Associative array of datatypes to values 0, 1, or 2.
     */
    protected $_numericDataTypes = array(
        Zend_Db::INT_TYPE    => Zend_Db::INT_TYPE,
        Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
        Zend_Db::FLOAT_TYPE  => Zend_Db::FLOAT_TYPE,
        'INT'                => Zend_Db::INT_TYPE,
        'SMALLINT'           => Zend_Db::INT_TYPE,
        'TINYINT'            => Zend_Db::INT_TYPE,
        'BIGINT'             => Zend_Db::BIGINT_TYPE,
        'DECIMAL'            => Zend_Db::FLOAT_TYPE,
        'FLOAT'              => Zend_Db::FLOAT_TYPE,
        'MONEY'              => Zend_Db::FLOAT_TYPE,
        'NUMERIC'            => Zend_Db::FLOAT_TYPE,
        'REAL'               => Zend_Db::FLOAT_TYPE,
        'SMALLMONEY'         => Zend_Db::FLOAT_TYPE
    );

    /**
     * Returns a list of the tables in the database.
     *
     * @return array
     */
    public function listTables()
    {
        $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
        $result = @mssql_query($sql, $this->getConnection());
        if (!is_resource($result)) {
            /**
             * @see Adapters_Db_Adapter_Mssql_Exception
             */
            require_once 'Adapters/Db/Adapter/Mssql/Exception.php';
            throw new Adapters_Db_Adapter_Mssql_Exception(
                'Unable to retrieve table names as query failed (' . mssql_get_last_message() . ')'
            );
        }

        $ret = array();
        while ($row = mssql_fetch_array($result)) {
            $ret[] = $row[0];
        }

        return $ret;
    }

    /**
     * Returns the column descriptions for a table.
     *
     * The return value is an associative array keyed by the column name,
     * as returned by the RDBMS.
     *
     * The value of each array element is an associative array
     * with the following keys:
     *
     * SCHEMA_NAME      => string; name of schema
     * TABLE_NAME       => string;
     * COLUMN_NAME      => string; column name
     * COLUMN_POSITION  => number; ordinal position of column in table
     * DATA_TYPE        => string; SQL datatype name of column
     * DEFAULT          => string; default expression of column, null if none
     * NULLABLE         => boolean; true if column can have nulls
     * LENGTH           => number; length of CHAR/VARCHAR
     * SCALE            => number; scale of NUMERIC/DECIMAL
     * PRECISION        => number; precision of NUMERIC/DECIMAL
     * UNSIGNED         => boolean; unsigned property of an integer type
     * PRIMARY          => boolean; true if column is part of the primary key
     * PRIMARY_POSITION => integer; position of column in primary key
     * IDENTITY         => integer; true if column is auto-generated with unique values
     *
     * @todo Discover integer unsigned property.
     *
     * @param string $tableName
     * @param string $schemaName OPTIONAL
     * @return array
     */
    public function describeTable($tableName, $schemaName = null)
    {
        /**
         * Discover metadata information about this table.
         */
        $sql    = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);
        $stmt   = $this->query($sql);
        $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);

		// ZF-7698
		$stmt->closeCursor();

        if (count($result) == 0) {
            return array();
        }

        $owner           = 1;
        $table_name      = 2;
        $column_name     = 3;
        $type_name       = 5;
        $precision       = 6;
        $length          = 7;
        $scale           = 8;
        $nullable        = 10;
        $column_def      = 12;
        $column_position = 16;

        /**
         * Discover primary key column(s) for this table.
         */
        $tableOwner = $result[0][$owner];
        $sql        = "exec sp_pkeys @table_owner = " . $tableOwner
                    . ", @table_name = " . $this->quoteIdentifier($tableName, true);
        $stmt       = $this->query($sql);

        $primaryKeysResult = $stmt->fetchAll(Zend_Db::FETCH_NUM);
        $primaryKeyColumn  = array();

        // Per http://msdn.microsoft.com/en-us/library/ms189813.aspx,
        // results from sp_keys stored procedure are:
        // 0=TABLE_QUALIFIER 1=TABLE_OWNER 2=TABLE_NAME 3=COLUMN_NAME 4=KEY_SEQ 5=PK_NAME

        $pkey_column_name = 3;
        $pkey_key_seq     = 4;
        foreach ($primaryKeysResult as $pkeysRow) {
            $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];
        }

        $desc = array();
        $p    = 1;
        foreach ($result as $key => $row) {
            $identity = false;
            $words    = explode(' ', $row[$type_name], 2);
            if (isset($words[0])) {
                $type = $words[0];
                if (isset($words[1])) {
                    $identity = (bool) preg_match('/identity/', $words[1]);
                }
            }

            $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);
            if ($isPrimary) {
                $primaryPosition = $primaryKeyColumn[$row[$column_name]];
            } else {
                $primaryPosition = null;
            }

            $desc[$this->foldCase($row[$column_name])] = array(
                'SCHEMA_NAME'      => null, // @todo
                'TABLE_NAME'       => $this->foldCase($row[$table_name]),
                'COLUMN_NAME'      => $this->foldCase($row[$column_name]),
                'COLUMN_POSITION'  => (int) $row[$column_position],
                'DATA_TYPE'        => $type,
                'DEFAULT'          => $row[$column_def],
                'NULLABLE'         => (bool) $row[$nullable],
                'LENGTH'           => $row[$length],
                'SCALE'            => $row[$scale],
                'PRECISION'        => $row[$precision],
                'UNSIGNED'         => null, // @todo
                'PRIMARY'          => $isPrimary,
                'PRIMARY_POSITION' => $primaryPosition,
                'IDENTITY'         => $identity,
            );
        }

        return $desc;
    }

    /**
     * Formats the hostname when a port is used.
     *
     * @return string
     */
    protected function _formatHostname($hostname, $port = null)
    {
        if (is_numeric($port)) {
            $seperator = ':';
            if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
                $seperator = ',';
            }
            $hostname .= $seperator . $port;
        }

        return $hostname;
    }

    /**
     * Set the MSSql "QUOTED_IDENTIFIER ON" option.
     *
     * @link http://msdn.microsoft.com/en-us/library/ms174393.aspx
     * @throws Adapters_Db_Adapter_Mssql_Exception
     */
    protected function _setQuotedIdentifierOn()
    {
        $result = @mssql_query('SET QUOTED_IDENTIFIER ON', $this->getConnection());
        if (!is_resource($result)) {
            /**
             * @see Adapters_Db_Adapter_Mssql_Exception
             */
            require_once 'Adapters/Db/Adapter/Mssql/Exception.php';
            throw new Adapters_Db_Adapter_Mssql_Exception(
                'Unable to "SET QUOTED_IDENTIFIER ON" as query failed (' . mssql_get_last_message() . ')'
            );
        }

        return (bool) $result;
    }

    /**
     * Creates a connection to the database.
     *
     * @return void
     */
    protected function _connect()
    {
        if ($this->isConnected()) {
            return;
        }

        if (!extension_loaded('mssql')) {
            /**
             * @see Adapters_Db_Adapter_Mssql_Exception
             */
            require_once 'Adapters/Db/Adapter/Mssql/Exception.php';
            throw new Adapters_Db_Adapter_Mssql_Exception(
                'The MsSQL extension is required for this adapter but the extension is not loaded.'
            );
        }

        $dsn = $this->_config;

        $hostname = $dsn['host'];
        if (array_key_exists('port', $dsn)) {
            $hostname = $this->_formatHostname($dsn['host'], $dsn['port']);
        }

        // Is this a persistent connection.
        $connectFunction = 'mssql_connect';
        if (array_key_exists('persistent', $dsn) && (true == $dsn['persistent'])) {
            $connectFunction = 'mssql_pconnect';
        }

        // Find out if new link should be created.
        $new = false;
        if (array_key_exists('options', $dsn) && is_array($dsn['options'])) {
            if (array_key_exists('new', $dsn['options']) && (true == $dsn['options']['new'])) {
                $new = true;
            }
        }

        $connection = @$connectFunction(
            $hostname,
            $dsn['username'],
            $dsn['password'],
            $new
        );

        if (!$connection) {
            /**
             * @see Adapters_Db_Adapter_Mssql_Exception
             */
            require_once 'Adapters/Db/Adapter/Mssql/Exception.php';
            throw new Adapters_Db_Adapter_Mssql_Exception(
                'MSSQL Connection Error: (' . mssql_get_last_message() . ')'
            );
        }

        $this->_connection = $connection;

        $dbSelect = mssql_select_db($dsn['dbname'], $this->getConnection());
        if (!$dbSelect) {
            /**
             * @see Adapters_Db_Adapter_Mssql_Exception
             */
            require_once 'Adapters/Db/Adapter/Mssql/Exception.php';
            throw new Adapters_Db_Adapter_Mssql_Exception(
                'MSSQL Database Select Error: ' . mssql_get_last_message()
            );
        }

        $this->_setQuotedIdentifierOn();
    }

    /**
     * Test if a connection is active
     *
     * @return boolean
     */
    public function isConnected()
    {
        return is_resource($this->_connection);
    }

    /**
     * Force the connection to close.
     *
     * @return void
     */
    public function closeConnection()
    {
        if ($this->isConnected()) {
            mssql_close($this->_connection);
        }
        $this->_connection = null;
    }

    /**
     * Prepare a statement and return a PDOStatement-like object.
     *
     * @param string|Zend_Db_Select $sql SQL query
     * @return Zend_Db_Statement|PDOStatement
     */
    public function prepare($sql)
    {
        trigger_error('This method has not yet been implemented.', E_USER_ERROR);
    }

    /**
     * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
     *
     * As a convention, on RDBMS brands that support sequences
     * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
     * from the arguments and returns the last id generated by that sequence.
     * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
     * returns the last value generated for such a column, and the table name
     * argument is disregarded.
     *
     * @param string $tableName   OPTIONAL Name of table.
     * @param string $primaryKey  OPTIONAL Name of primary key column.
     * @return string
     */
    public function lastInsertId($tableName = null, $primaryKey = null)
    {
        $sql = 'SELECT SCOPE_IDENTITY()';
        $result = @mssql_query($sql, $this->getConnection());
        if (!is_resource($result)) {
            /**
             * @see Adapters_Db_Adapter_Mssql_Exception
             */
            require_once 'Adapters/Db/Adapter/Mssql/Exception.php';
            throw new Adapters_Db_Adapter_Mssql_Exception(
                'Unable to retrieve last inserted ID as query failed (' . mssql_get_last_message() . ')'
            );
        }

        list($lastInsertId) = mssql_fetch_row($result);

        return $lastInsertId;
    }

    /**
     * Begin a transaction.
     */
    protected function _beginTransaction()
    {
        return (bool) @mssql_query('BEGIN TRANSACTION', $this->getConnection());
    }

    /**
     * Commit a transaction.
     */
    protected function _commit()
    {
        return (bool) @mssql_query('COMMIT TRANSACTION', $this->getConnection());
    }

    /**
     * Roll-back a transaction.
     */
    protected function _rollBack()
    {
        return (bool) @mssql_query('ROLLBACK TRANSACTION', $this->getConnection());
    }

    /**
     * Set the fetch mode.
     *
     * @param integer $mode
     * @return void
     * @throws Zend_Db_Adapter_Exception
     */
    public function setFetchMode($mode)
    {
        trigger_error('This method has not yet been implemented.', E_USER_ERROR);
    }

    /**
     * Adds an adapter-specific LIMIT clause to the SELECT statement.
     *
     * @param mixed $sql
     * @param integer $count
     * @param integer $offset
     * @return string
     */
    public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0) {
            /** @see Zend_Db_Adapter_Exception */
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /** @see Zend_Db_Adapter_Exception */
            require_once 'Zend/Db/Adapter/Exception.php';
            throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
        }

        $sql = preg_replace(
            '/^SELECT\s+(DISTINCT\s)?/i',
            'SELECT $1TOP ' . ($count+$offset) . ' ',
            $sql
        );

        if ($offset > 0) {
            $orderby = stristr($sql, 'ORDER BY');

            if ($orderby !== false) {
                $orderParts = explode(',', substr($orderby, 8));
                $pregReplaceCount = null;
                $orderbyInverseParts = array();
                foreach ($orderParts as $orderPart) {
                    $orderPart = rtrim($orderPart);
                    $inv = preg_replace('/\s+desc$/i', ' ASC', $orderPart, 1, $pregReplaceCount);
                    if ($pregReplaceCount) {
                        $orderbyInverseParts[] = $inv;
                        continue;
                    }
                    $inv = preg_replace('/\s+asc$/i', ' DESC', $orderPart, 1, $pregReplaceCount);
                    if ($pregReplaceCount) {
                        $orderbyInverseParts[] = $inv;
                        continue;
                    } else {
                        $orderbyInverseParts[] = $orderPart . ' DESC';
                    }
                }

                $orderbyInverse = 'ORDER BY ' . implode(', ', $orderbyInverseParts);
            }

            $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
            if ($orderby !== false) {
                $sql .= ' ' . $orderbyInverse . ' ';
            }
            $sql .= ') AS outer_tbl';
            if ($orderby !== false) {
                $sql .= ' ' . $orderby;
            }
        }

        return $sql;
    }

    /**
     * Check if the adapter supports real SQL parameters.
     *
     * @param string $type 'positional' or 'named'
     * @return bool
     */
    public function supportsParameters($type)
    {
        switch ($type) {
            case 'positional':
                return true;
            case 'named':
            default:
                return false;
        }
    }

    /**
     * Retrieve server version in PHP style
     *
     * @return string
     */
    public function getServerVersion()
    {
        $version = @mssql_query("SELECT SERVERPROPERTY('productversion')", $this->getConnection());
        if (!is_resource($version)) {
            /**
             * @see Adapters_Db_Adapter_Mssql_Exception
             */
            require_once 'Adapters/Db/Adapter/Mssql/Exception.php';
            throw new Adapters_Db_Adapter_Mssql_Exception(
                'Unable to retrieve server version as query failed (' . mssql_get_last_message() . ')'
            );
        }

        list($version) = mssql_fetch_row($version);

        return $version;
    }
}